﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class SachDAO : AbstractDAO
    {
        public static List<SachDTO> TimKiemSachDayDu(string maSach, string tenSach, string tenTheLoai, string tenTacGia)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND S.MaSach=@MaSach AND S.TenSach=@TenSach AND TL.TenTheLoai=@TenTheLoai AND TG.TenTacGia=@TenTacGia";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaSach", OleDbType.VarChar);
                thamSo.Value = maSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenSach", OleDbType.VarChar);
                thamSo.Value = tenSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTacGia", OleDbType.VarChar);
                thamSo.Value = tenTacGia;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSach

        public static List<SachDTO> TimKiemSachTheoTenTheLoai(string tenTheLoai)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND TL.TenTheLoai=@TenTheLoai";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTheLoai

        public static List<SachDTO> TimKiemSachTheoTLoaiMSach(string tenTheLoai, string maSach)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND S.MaSach=@MaSach AND TL.TenTheLoai=@TenTheLoai";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaSach", OleDbType.VarChar);
                thamSo.Value = maSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTLoaiMSach

        public static List<SachDTO> TimKiemSachTheoTLoaiTSach(string tenTheLoai, string tenSach)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND S.TenSach=@TenSach AND TL.TenTheLoai=@TenTheLoai";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@TenSach", OleDbType.VarChar);
                thamSo.Value = tenSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTLoaiTSach

        public static List<SachDTO> TimKiemSachTheoTLoaiTTacGia(string tenTheLoai, string tenTacGia)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND TG.TenTacGia=@TenTacGia AND TL.TenTheLoai=@TenTheLoai";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@TenTacGia", OleDbType.VarChar);
                thamSo.Value = tenTacGia;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTLoaiTTacGia

        public static List<SachDTO> TimKiemSachTheoTLoaiMSachTSach(string tenTheLoai, string maSach, string tenSach)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND S.MaSach=@MaSach AND S.TenSach=@TenSach AND TL.TenTheLoai=@TenTheLoai";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaSach", OleDbType.VarChar);
                thamSo.Value = maSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenSach", OleDbType.VarChar);
                thamSo.Value = tenSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTLoaiMSachTSach

        public static List<SachDTO> TimKiemSachTheoTLoaiMSachTTacGia(string tenTheLoai, string maSach, string tenTacGia)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND S.MaSach=@MaSach AND TL.TenTheLoai=@TenTheLoai AND TG.TenTacGia=@TenTacGia";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaSach", OleDbType.VarChar);
                thamSo.Value = maSach;
                lenh.Parameters.Add(thamSo);
            
                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTacGia", OleDbType.VarChar);
                thamSo.Value = tenTacGia;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTLoaiMSachTTacGia

        public static List<SachDTO> TimKiemSachTheoTLoaiTSachTTacGia(string tenTheLoai, string tenSach, string tenTacGia)
        {
            // Mo ket noi va tao ra danh sach sach
            OleDbConnection connect = null;
            List<SachDTO> dsSach = new List<SachDTO>();

            try
            {
                connect = MoKetNoi();
                string chuoiLenh = "SELECT S.ID,S.MaSach,S.TenSach,S.SoLuongTon,TL.ID,TL.MaTheLoai,TL.TenTheLoai,TG.ID,TG.MaTacGia,TG.TenTacGia,S.GiaBan FROM [SACH] S, [THE LOAI] TL, [TAC GIA] TG WHERE S.MaTheLoai=TL.MaTheLoai AND S.MaTacGia=TG.MaTacGia ";
                chuoiLenh += "AND S.TenSach=@TenSach AND TL.TenTheLoai=@TenTheLoai AND TG.TenTacGia=@TenTacGia";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, connect);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@TenSach", OleDbType.VarChar);
                thamSo.Value = tenSach;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTheLoai", OleDbType.VarChar);
                thamSo.Value = tenTheLoai;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenTacGia", OleDbType.VarChar);
                thamSo.Value = tenTacGia;
                lenh.Parameters.Add(thamSo);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    SachDTO sach = new SachDTO();
                    if (boDoc.GetInt32(0) != 0)
                        sach.SachID = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        sach.MaSach = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        sach.TenSach = boDoc.GetString(2);
                    if (boDoc.GetInt32(3) >= 0)
                        sach.SoLuongTon = boDoc.GetInt32(3);
                    TheLoaiDTO TheLoai = new TheLoaiDTO();
                    if (boDoc.GetInt32(4) >= 0)
                        TheLoai.TheLoaiID = boDoc.GetInt32(4);
                    if (!boDoc.IsDBNull(5))
                        TheLoai.MaTheLoai = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        TheLoai.TenTheLoai = boDoc.GetString(6);
                    sach.TheLoai = TheLoai;
                    TacGiaDTO TacGia = new TacGiaDTO();
                    if (boDoc.GetInt32(7) >= 0)
                        TacGia.TacGiaID = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        TacGia.MaTacGia = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        TacGia.TenTacGia = boDoc.GetString(9);
                    sach.TacGia = TacGia;
                    if (boDoc.GetInt32(10) >= 0)
                        sach.GiaBan = boDoc.GetInt32(10);
                    dsSach.Add(sach);
                } // ket thuc while
            } // ket thuc try
            catch (Exception ex)
            {
                dsSach = new List<SachDTO>();
            } // ket thuc catch
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            } // ket thuc finally

            // tra ve danh sach sach tim duoc
            return dsSach;
        } // ket thuc phuong thuc TimKiemSachTheoTLoaiTSachTTacGia
    } // ket thuc class SachDAO
}
